import express from 'express';
import xlsx from 'xlsx';
import cors from 'cors';
import CryptoJS from 'crypto-js';
import jwt from 'jsonwebtoken';
import { fileURLToPath } from 'url';
import path from 'path';
import axios from 'axios';
import mysql from 'mysql2';

const app = express();
const PORT = 3000;
const SECRET_KEY = 'my_secret_key_123';
const JWT_SECRET = 'jwt_secret_key_456';
const PASSWORD_SECRET = 'password_secret_456';

app.use(cors());
app.use(express.json());

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 创建 MySQL 连接
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: '1234',
  database: 'word_memory'
});

// 连接数据库
db.connect((err) => {
  if (err) {
    console.error('数据库连接失败:', err);
    process.exit(1);
  }
  console.log('成功连接到数据库');
});

// 用户登录接口
app.post('/login', (req, res) => {
  const { username, password } = req.body;

  const login_ip = '192.168.1.1';
  const user_agent = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36';

  try {
    const decryptedPassword1 = CryptoJS.AES.decrypt(password, PASSWORD_SECRET).toString(CryptoJS.enc.Utf8);

    const query = 'SELECT * FROM users WHERE username = ?';
    db.execute(query, [username], (err, results) => {
      if (err) {
        console.error('数据库查询失败:', err);
        return res.status(500).json({ success: false, message: '服务器错误' });
      }

      const user = results[0];
      if (!user) {
        logLoginAttempt(username, login_ip, user_agent, 'failed');
        return res.status(401).json({ success: false, message: '用户名或密码错误' });
      }

      // 🚫 封禁状态检查
      if (user.status === 0) {
        logLoginAttempt(username, login_ip, user_agent, 'banned');
        return res.status(403).json({ success: false, message: '账号已被封禁，请联系管理员' });
      }

      const decryptedPassword = CryptoJS.AES.decrypt(user.password, PASSWORD_SECRET).toString(CryptoJS.enc.Utf8);

      if (decryptedPassword !== decryptedPassword1) {
        logLoginAttempt(username, login_ip, user_agent, 'failed');
        return res.status(401).json({ success: false, message: '用户名或密码错误' });
      }

      const token = jwt.sign({ username: user.username }, JWT_SECRET, { expiresIn: '2h' });

      res.setHeader('Authorization', `Bearer ${token}`);
      res.json({
        success: true,
        message: '登录成功',
        userId: user.id
      });

      logLoginAttempt(username, login_ip, user_agent, 'success', user.id);
    });
  } catch (error) {
    console.error('服务器错误:', error);
    res.status(500).json({ success: false, message: '服务器错误' });
  }
});
// 修改密码接口
app.post('/changePassword', (req, res) => {
  const { userId, oldPassword, newPassword } = req.body;
  // console.log(userId, oldPassword, newPassword);
  // 参数校验
  if (!userId || !oldPassword || !newPassword) {
    return res.status(400).json({ success: false, message: '用户ID、旧密码和新密码不能为空' });
  }

  // 查询用户
  const query = 'SELECT * FROM users WHERE id = ?';
  db.execute(query, [userId], (err, results) => {
    if (err) {
      console.error('数据库查询错误:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    if (results.length === 0) {
      return res.status(404).json({ success: false, message: '用户不存在' });
    }

    const user = results[0];

    // 解密原密码进行验证
    const decryptedPassword = CryptoJS.AES.decrypt(user.password, PASSWORD_SECRET).toString(CryptoJS.enc.Utf8);

    if (decryptedPassword !== oldPassword) {
      return res.status(400).json({ success: false, message: '旧密码不正确' });
    }

    // 加密新密码
    const encryptedNewPassword = CryptoJS.AES.encrypt(newPassword, PASSWORD_SECRET).toString();

    // 更新密码
    const updateQuery = 'UPDATE users SET password = ? WHERE id = ?';
    db.execute(updateQuery, [encryptedNewPassword, userId], (err, result) => {
      if (err) {
        console.error('更新密码失败:', err);
        return res.status(500).json({ success: false, message: '服务器错误' });
      }

      res.json({ success: true, message: '密码修改成功' });
    });
  });
});



// 记录登录日志的辅助函数
function logLoginAttempt(username, login_ip, user_agent, login_status, user_id = null) {
  const query = 'INSERT INTO user_login_log (user_id, login_ip, user_agent, login_status) VALUES (?, ?, ?, ?)';
  const values = [user_id, login_ip, user_agent, login_status];

  // 如果是失败的登录，不需要 user_id
  if (login_status === 'failed') {
    db.execute(query, [null, login_ip, user_agent, login_status], (err, results) => {
      if (err) {
        console.error('记录登录日志失败:', err);
      }
    });
  } else {
    db.execute(query, values, (err, results) => {
      if (err) {
        console.error('记录登录日志失败:', err);
      }
    });
  }
}
// 用户退出更新日志
app.post('/logout', (req, res) => {
  const userId = req.body.userId; // 或从token里解析出来
  // console.log(userId, '用户退出更新日志');
  // 找到用户最近的一次登录成功记录
  const query = `
    UPDATE user_login_log
    SET logout_time = NOW()
    WHERE user_id = ? AND login_status = 'success'
    ORDER BY login_time DESC
    LIMIT 1
  `;

  db.execute(query, [userId], (err, results) => {
    if (err) {
      console.error('更新退出时间失败:', err);
      return res.status(500).json({ success: false, message: '退出失败' });
    }

    return res.json({ success: true, message: '退出成功' });
  });
});


// 用户注册接口
app.post('/register', (req, res) => {
  const { username, password, email } = req.body;
  // console.log(username, password, email, '用户注册接口');
  // 检查用户名、密码、邮箱是否为空
  if (!username || !password || !email) {
    return res.status(400).json({ success: false, message: '用户名、密码和邮箱不能为空' });
  }

  // 邮箱格式验证
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
  if (!emailRegex.test(email)) {
    return res.status(400).json({ success: false, message: '邮箱格式不正确' });
  }

  // 查询用户名是否已存在
  const checkUserQuery = 'SELECT * FROM users WHERE username = ?';
  db.execute(checkUserQuery, [username], (err, results) => {
    if (err) {
      console.error('数据库查询失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    if (results.length > 0) {
      return res.status(400).json({ success: false, message: '用户名已存在' });
    }

    // 查询邮箱是否已存在
    const checkEmailQuery = 'SELECT * FROM users WHERE email = ?';
    db.execute(checkEmailQuery, [email], (err, results) => {
      if (err) {
        console.error('数据库查询失败:', err);
        return res.status(500).json({ success: false, message: '服务器错误' });
      }

      if (results.length > 0) {
        return res.status(400).json({ success: false, message: '邮箱已存在' });
      }

      // 加密密码
      const encryptedPassword = CryptoJS.AES.encrypt(password, PASSWORD_SECRET).toString();

      // 随机生成 avatar1 - avatar5
      const avatarNumber = Math.floor(Math.random() * 5) + 1;
      const avatar = `avatar${avatarNumber}`;

      // 插入用户到数据库
      const insertQuery = 'INSERT INTO users (username, password, avatar, email) VALUES (?, ?, ?, ?)';
      db.execute(insertQuery, [username, encryptedPassword, avatar, email], (err, result) => {
        if (err) {
          console.error('用户注册失败:', err);
          return res.status(500).json({ success: false, message: '服务器错误' });
        }

        res.json({ success: true, message: '注册成功' });
      });
    });
  });
});


// 获取用户数据接口
app.get('/users/:id', (req, res) => {
  const userId = req.params.id;

  // 查询数据库中的用户信息
  const query = 'SELECT id, username, email, phone, avatar, created_at FROM users WHERE id = ?';

  db.query(query, [userId], (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ error: '服务器错误' });
    }

    if (results.length === 0) {
      return res.status(404).json({ message: '用户未找到' });
    }

    // 返回用户数据
    res.status(200).json(results[0]);
  });
});


// 获取单词数据的 API 路由
app.get('/getWordsData', (req, res) => {
  const query = 'SELECT * FROM words';

  db.query(query, (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ message: '查询失败' });
    }

    res.status(200).json({
      message: '获取单词数据成功',
      data: results // 返回查询到的所有单词数据
    });
  });
});




// 学习新单词
app.post('/get-unlearned-words', (req, res) => {
  const user_id = parseInt(req.body.user_id, 10);
  const limit = parseInt(req.body.limit, 10);

  // 确保参数有效
  if (isNaN(user_id) || isNaN(limit)) {
    return res.status(400).json({ success: false, message: 'Invalid input parameters' });
  }

  // 拼接 LIMIT 参数
  const query = `
    SELECT w.id AS word_id, w.word, w.definition, w.phonetic
    FROM words w
    LEFT JOIN user_words uw ON w.id = uw.word_id AND uw.user_id = ?
    WHERE uw.word_id IS NULL
    LIMIT ?;
  `;

  // 使用 query 方法执行查询
  db.query(query, [user_id, limit], (err, results) => {
    if (err) {
      console.error('数据库查询失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    res.json({ success: true, words: results });
  });
});


// 插入学习的单词
app.post('/insert-user-words', (req, res) => {
  const { userId, learnedWords } = req.body;  // 前端传递的用户ID和学习过的单词数组
  console.log(userId, learnedWords);

  // 输入验证
  if (!userId || !learnedWords || learnedWords.length === 0) {
    return res.status(400).json({ success: false, message: '无效的输入数据' });
  }

  // 确保 userId 是有效的整数
  const validUserId = parseInt(userId, 10);
  if (isNaN(validUserId)) {
    return res.status(400).json({ success: false, message: '无效的用户ID' });
  }

  // 当前时间，MySQL 兼容格式
  const currentTime = new Date().toISOString().slice(0, 19).replace('T', ' '); // 转换为 'YYYY-MM-DD HH:MM:SS'
  const nextStudyTime = new Date(new Date().getTime() + 24 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');  // 默认下一次学习时间为 24 小时后

  // 准备批量插入的数据
  const insertData = learnedWords.map(word => {
    return [
      validUserId,  // 用户ID
      word.word_id,  // 单词ID
      currentTime,  // 上次学习时间
      nextStudyTime,  // 下次学习时间
      word.proficiency || 0,  // 学习熟练度，默认为 0
    ];
  });

  console.log(insertData);

  // SQL 插入语句
  const query = `
    INSERT INTO user_words (user_id, word_id, last_study_time, next_study_time, proficiency)
    VALUES ?
  `;

  db.query(query, [insertData], (err, result) => {
    if (err) {
      console.error('批量插入学习记录失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    res.json({ success: true, message: '学习记录插入成功' });
  });
});



// 查询用户学习统计信息
app.get('/user-stats/:user_id', (req, res) => {
  const user_id = parseInt(req.params.user_id, 10);

  // 确保参数有效
  if (isNaN(user_id)) {
    return res.status(400).json({ success: false, message: 'Invalid user ID' });
  }

  const query = `
    SELECT 
      u.id AS user_id,
      u.username,
      (SELECT COUNT(*) FROM words) AS total_words,
      (SELECT COUNT(*) FROM user_words WHERE user_id = ?) AS learned_words,
      (SELECT COUNT(*) FROM words) - (SELECT COUNT(*) FROM user_words WHERE user_id = ?) AS unlearned_words
    FROM 
      users u
    WHERE 
      u.id = ?;
  `;

  // 使用 query 方法执行查询
  db.query(query, [user_id, user_id, user_id], (err, results) => {
    if (err) {
      console.error('数据库查询失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    if (results.length === 0) {
      return res.status(404).json({ success: false, message: '用户不存在' });
    }

    res.json({
      success: true,
      stats: {
        user_id: results[0].user_id,
        username: results[0].username,
        total_words: results[0].total_words,
        learned_words: results[0].learned_words,
        unlearned_words: results[0].unlearned_words,
        learning_progress: (results[0].learned_words / results[0].total_words * 100).toFixed(2) + '%'
      }
    });
  });
});


// 插入笔记记录
app.post('/addNote', (req, res) => {
  let { user_id, word_id, note } = req.body;

  // 校验字段
  if (!user_id || !word_id || !note) {
    return res.status(400).send('缺少必要的字段');
  }

  // 转换 ID 为整数
  user_id = parseInt(user_id, 10);
  word_id = parseInt(word_id, 10);
  console.log(`收到请求：user_id=${user_id}, word_id=${word_id}, note=${note}`);

  // 检查转换是否有效
  if (isNaN(user_id) || isNaN(word_id)) {
    return res.status(400).send('user_id 或 word_id 必须是有效的整数');
  }

  // 插入笔记，同时设置 updated_at 为当前时间
  const query = `
    INSERT INTO notes (user_id, word_id, note, updated_at)
    VALUES (?, ?, ?, NOW())
  `;

  db.query(query, [user_id, word_id, note], (err, result) => {
    if (err) {
      console.error('插入笔记失败:', err);
      return res.status(500).send('服务器错误');
    }
    res.status(200).send('笔记添加成功');
  });
});

// 查询用户单词笔记记录
app.get('/getNotes', (req, res) => {
  let { user_id, word_id } = req.query;

  // 确保 user_id 和 word_id 都已提供
  if (!user_id || !word_id) {
    return res.status(400).send('缺少必要的字段');
  }

  // 将 user_id 和 word_id 转换为整数
  user_id = parseInt(user_id, 10);
  word_id = parseInt(word_id, 10);
  // console.log(`收到请求：user_id=${user_id}, word_id=${word_id}`);

  // 检查转换是否成功
  if (isNaN(user_id) || isNaN(word_id)) {
    return res.status(400).send('user_id 或 word_id 必须是有效的整数');
  }

  const query = `
    SELECT n.id, n.user_id, n.word_id, n.note, n.created_at, n.updated_at
    FROM notes n
    WHERE n.user_id = ? AND n.word_id = ?;
  `;

  db.query(query, [user_id, word_id], (err, result) => {
    if (err) {
      console.error('查询笔记失败:', err);
      return res.status(500).send('服务器错误');
    }

    if (result.length === 0) {
      return res.status(404).send('没有找到该单词的笔记');
    }

    res.status(200).json(result);
  });
});
// 删除笔记
app.delete('/deleteNote/:note_id', (req, res) => {
  const { note_id } = req.params;
  // console.log(note_id);

  if (!note_id) {
    return res.status(400).json({ message: '缺少 note_id 参数' });
  }

  const query = 'DELETE FROM notes WHERE id = ?';
  db.query(query, [note_id], (err, result) => {
    if (err) {
      console.error('删除笔记失败:', err);
      return res.status(500).json({ message: '服务器错误，删除笔记失败' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到要删除的笔记' });
    }

    res.json({ message: '笔记删除成功' });
  });
});
// 编辑笔记接口
app.put('/editNote/:note_id', (req, res) => {
  const { note_id } = req.params;
  const { note } = req.body;
  console.log(note_id, note, 'xsacascaca');

  if (!note_id || !note) {
    return res.status(400).json({ message: '缺少 note_id 或 note 内容' });
  }

  const query = 'UPDATE notes SET note = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?';
  db.query(query, [note, note_id], (err, result) => {
    if (err) {
      console.error('更新笔记失败:', err);
      return res.status(500).json({ message: '服务器错误，更新失败' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ message: '未找到该笔记' });
    }

    res.json({ message: '笔记更新成功' });
  });
});

//用户签到接口（签到打卡）
app.post('/signin', (req, res) => {
  const { user_id } = req.body;

  if (!user_id) {
    return res.status(400).json({ message: '缺少 user_id' });
  }

  const today = new Date().toISOString().slice(0, 10); // 获取 yyyy-mm-dd 格式

  // 检查今天是否已经签到
  const checkQuery = 'SELECT * FROM user_signins WHERE user_id = ? AND signin_date = ?';
  db.query(checkQuery, [user_id, today], (err, results) => {
    if (err) {
      console.error('查询签到记录失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    if (results.length > 0) {
      return res.status(400).json({ message: '今天已经签到过了' });
    }

    // 插入签到记录
    const insertQuery = 'INSERT INTO user_signins (user_id, signin_date) VALUES (?, ?)';
    db.query(insertQuery, [user_id, today], (err, result) => {
      if (err) {
        console.error('签到失败:', err);
        return res.status(500).json({ message: '服务器错误，签到失败' });
      }

      res.json({ message: '签到成功', id: result.insertId });
    });
  });
});
//查询用户签到记录接口
app.get('/signin-records/:user_id', (req, res) => {
  const { user_id } = req.params;

  const query = 'SELECT signin_date, signin_time FROM user_signins WHERE user_id = ? ORDER BY signin_date DESC';
  db.query(query, [user_id], (err, results) => {
    if (err) {
      console.error('获取签到记录失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    res.json({ message: '查询成功', records: results });
  });
});

// 获取需要复习的单词
// 获取需要复习的单词（支持限制返回数量）
app.get('/review-words', (req, res) => {
  const userId = parseInt(req.query.user_id, 10);
  const limit = parseInt(req.query.limit, 10) || 10; // 默认一次拉取10个

  // console.log(userId,limit);



  if (isNaN(userId) || userId <= 0) {
    return res.status(400).json({ message: '无效的用户ID' });
  }

  const query = `
    SELECT w.id, w.word, w.definition, w.phonetic, uw.proficiency, uw.next_study_time
    FROM user_words uw
    JOIN words w ON uw.word_id = w.id
    WHERE uw.user_id = ? AND uw.next_study_time <= NOW()
    ORDER BY uw.next_study_time ASC
    LIMIT ?
  `;

  db.query(query, [userId, limit], (err, results) => {
    if (err) {
      console.error('查询需要复习的单词失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    res.json({ success: true, words: results });
    // console.log(results);

  });
});
// 获取当前需要复习的单词总数
app.get('/review-word-count', (req, res) => {
  const userId = parseInt(req.query.user_id, 10);

  if (isNaN(userId) || userId <= 0) {
    return res.status(400).json({ message: '无效的用户ID' });
  }

  const query = `
    SELECT COUNT(*) AS count
    FROM user_words
    WHERE user_id = ? AND next_study_time <= NOW()
  `;

  db.query(query, [userId], (err, results) => {
    if (err) {
      console.error('统计需要复习的单词失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    res.json({ success: true, count: results[0].count });
  });
});



// 批量更新单词的复习时间
// update-review-time
app.post('/update-review-time', (req, res) => {
  const { userId, words } = req.body;
  const user_id = parseInt(userId);
  // console.log('user_id:', user_id, words);

  if (!user_id || !Array.isArray(words)) {
    return res.status(400).json({ message: '参数缺失或格式错误' });
  }

  const reviewDaysMapping = [1, 3, 7, 14, 30, 50, 90];

  const updates = words.map(word => {
    return new Promise((resolve, reject) => {
      const { id: word_id, proficiency } = word;

      const newProficiency = Math.min(proficiency + 1, reviewDaysMapping.length - 1);
      const daysToAdd = reviewDaysMapping[proficiency]; // 用旧的 proficiency 算复习时间
      const nextStudyTime = new Date();
      nextStudyTime.setDate(nextStudyTime.getDate() + daysToAdd);

      const sql = `
        UPDATE user_words 
        SET next_study_time = ?, proficiency = ?
        WHERE user_id = ? AND word_id = ?
      `;
      db.query(sql, [nextStudyTime, newProficiency, user_id, word_id], (err, result) => {
        if (err) {
          console.error(`更新单词 ${word_id} 时出错:`, err);
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  });

  Promise.all(updates)
    .then(() => {
      res.json({ message: '单词复习信息更新成功（按旧熟练度定时间，熟练度+1）' });
    })
    .catch((err) => {
      res.status(500).json({ message: '更新过程中出错', error: err });
    });
});

// 处理论坛插入文章的接口
app.post('/api/forum_articles', (req, res) => {
  const { title, word, excerpt, category, votes, comments, user_id, content } = req.body;

  // 确保数据的完整性（必要字段检查）
  if (!title || !word || !category || !user_id) {
    return res.status(400).json({ message: '缺少必要的字段' });
  }

  // 文章插入SQL语句
  const sql = `
    INSERT INTO forum_articles (title, word, excerpt, category, votes, comments, date, user_id,content)
    VALUES (?, ?, ?, ?, ?, ?, NOW(), ?,?)
  `;

  // 执行插入操作
  db.execute(sql, [title, word, excerpt, category, votes || 0, comments || 0, user_id, content], (err, result) => {
    if (err) {
      console.error('插入文章失败:', err);
      return res.status(500).json({ message: '插入失败', error: err });
    }

    // 插入成功，返回文章ID
    return res.status(201).json({
      message: '文章插入成功',
      articleId: result.insertId
    });
  });
});
// 获取所有论坛文章的接口
// 获取所有论坛文章的接口
// app.get('/api/forum_articles', (req, res) => {
//   const query = `
//     SELECT fa.id, fa.title, fa.word, fa.excerpt, fa.category, fa.votes, fa.comments, fa.date, fa.user_id, u.username AS author_name
//     FROM forum_articles fa
//     JOIN users u ON fa.user_id = u.id
//   `;

//   db.query(query, (err, results) => {
//     if (err) {
//       console.error('查询失败:', err);
//       return res.status(500).json({ error: '查询失败' });
//     }

//     // 返回查询结果
//     res.json(results);
//     // console.log(results);  // 打印查询结果，检查数据
//   });
// });

app.get('/api/forum_articles', (req, res) => {
  const query = `
    SELECT fa.id, fa.title, fa.word, fa.excerpt, fa.category, fa.votes, fa.comments, fa.date, fa.user_id, 
           u.username AS author_name, u.avatar AS author_avatar
    FROM forum_articles fa
    JOIN users u ON fa.user_id = u.id
  `;

  db.query(query, (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ error: '查询失败' });
    }

    // 返回查询结果，包含用户头像
    res.json(results);
  });
});
// 查询文章详细数据
// 查询文章详细数据
app.get('/api/forum_articles/:id', (req, res) => {
  const articleId = req.params.id;

  const query = `
    SELECT fa.id, fa.title, fa.word, fa.excerpt, fa.category, fa.votes, fa.comments, fa.date, fa.user_id,
           fa.content,  -- 👈 加上 content 字段
           u.username AS author_name, u.avatar AS author_avatar
    FROM forum_articles fa
    JOIN users u ON fa.user_id = u.id
    WHERE fa.id = ?
  `;

  db.query(query, [articleId], (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ error: '查询失败' });
    }

    if (results.length === 0) {
      return res.status(404).json({ error: '文章不存在' });
    }

    res.json(results[0]); // 返回单篇文章，包含 content
  });
});



// 处理插入评论的接口
app.post('/api/article_comments', (req, res) => {
  const { article_id, user_id, comment } = req.body;

  // 检查必要字段
  if (!article_id || !user_id || !comment) {
    return res.status(400).json({ message: '缺少必要的字段' });
  }

  // 插入评论的 SQL
  const insertCommentSql = `
    INSERT INTO article_comments (article_id, user_id, comment, created_at)
    VALUES (?, ?, ?, NOW())
  `;

  // 插入评论
  db.execute(insertCommentSql, [article_id, user_id, comment], (err, result) => {
    if (err) {
      console.error('插入评论失败:', err);
      return res.status(500).json({ message: '插入失败', error: err });
    }

    const commentId = result.insertId;

    // 更新 forum_articles 中对应文章的评论数 +1
    const updateArticleSql = `
      UPDATE forum_articles
      SET comments = comments + 1
      WHERE id = ?
    `;

    db.execute(updateArticleSql, [article_id], (updateErr) => {
      if (updateErr) {
        console.error('更新评论数量失败:', updateErr);
        return res.status(500).json({
          message: '评论插入成功，但更新文章评论数量失败',
          commentId,
          error: updateErr
        });
      }

      // 全部成功
      return res.status(201).json({
        message: '评论插入成功，评论数已更新',
        commentId
      });
    });
  });
});

// 处理获取文章评论的接口
app.get('/api/article_comments/:article_id', (req, res) => {
  const { article_id } = req.params;

  const sql = `
    SELECT ac.id, ac.comment, ac.created_at, u.username, u.avatar, ac.user_id
    FROM article_comments ac
    JOIN users u ON ac.user_id = u.id
    WHERE ac.article_id = ?
    ORDER BY ac.created_at DESC
  `;

  db.execute(sql, [article_id], (err, result) => {
    if (err) {
      console.error('获取评论失败:', err);
      return res.status(500).json({ message: '获取失败', error: err });
    }

    return res.status(200).json({
      message: '评论获取成功',
      comments: result
    });
  });
});

// 根据用户ID获取该用户的所有文章
app.get('/api/articles/user/:userId', (req, res) => {
  const userId = req.params.userId;

  const sql = `
    SELECT id, title, word, excerpt, category, votes, comments, date, content
    FROM forum_articles
    WHERE user_id = ?
    ORDER BY date DESC
  `;

  db.query(sql, [userId], (err, results) => {
    if (err) {
      console.error('查询文章失败:', err);
      return res.status(500).json({ error: '数据库查询出错' });
    }

    res.json(results);
  });
});
// 根据用户id和文章id删除文章
// 根据用户ID和文章ID删除文章
app.delete('/api/articles/user/:userId/:articleId', (req, res) => {
  const { userId, articleId } = req.params;

  const sql = `
    DELETE FROM forum_articles
    WHERE user_id = ? AND id = ?
  `;

  db.query(sql, [userId, articleId], (err, result) => {
    if (err) {
      console.error('删除文章失败:', err);
      return res.status(500).json({ error: '数据库操作出错' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: '未找到对应的文章或无权限删除' });
    }

    res.json({ success: true, message: '文章删除成功' });
  });
});
// 根据用户ID和文章ID更新文章
// 根据用户ID和文章ID更新文章
app.put('/api/articles/user/:userId/:articleId', (req, res) => {
  const formatDateForMySQL = (isoString) => {
    let date = new Date(isoString);
    const pad = (n) => (n < 10 ? '0' + n : n);

    const Y = date.getFullYear();
    const M = pad(date.getMonth() + 1);
    const D = pad(date.getDate());
    const h = pad(date.getHours());
    const m = pad(date.getMinutes());
    const s = pad(date.getSeconds());

    return `${Y}-${M}-${D} ${h}:${m}:${s}`; // '2025-04-14 15:02:47'
  };

  const { userId, articleId } = req.params;
  const {
    title,
    content,
    category,
    excerpt,
    word,
    votes,
    comments,
    date
  } = req.body;

  const formattedDate = formatDateForMySQL(date); // ✅ 修复赋值问题

  const sql = `
    UPDATE forum_articles
    SET title = ?, content = ?, category = ?, excerpt = ?, word = ?, votes = ?, comments = ?, date = ?
    WHERE user_id = ? AND id = ?
  `;

  const values = [title, content, category, excerpt, word, votes, comments, formattedDate, userId, articleId];

  db.query(sql, values, (err, result) => {
    if (err) {
      console.error('更新文章失败:', err);
      return res.status(500).json({ error: '数据库更新出错' });
    }

    if (result.affectedRows === 0) {
      return res.status(404).json({ error: '未找到对应文章或无权限修改' });
    }

    res.json({ success: true, message: '文章更新成功' });
  });
});



// 添加学习日志接口
app.post('/addStudyLog', (req, res) => {
  const { user_id, study_date, start_time, end_time, duration_minutes, word_count, session_type } = req.body;

  // 校验必要字段
  if (!user_id || !study_date || !session_type) {
    return res.status(400).json({ success: false, message: '缺少必要的字段（user_id, study_date, session_type）' });
  }

  // 校验 session_type 是否合法
  if (!['study', 'review'].includes(session_type)) {
    return res.status(400).json({ success: false, message: 'session_type 必须是 "study" 或 "review"' });
  }

  // 这里我们信任前端传入的 duration_minutes，无需重新计算

  // 插入学习日志记录到数据库
  const insertQuery = `
    INSERT INTO user_study_log (user_id, study_date, start_time, end_time, duration_minutes, word_count, session_type)
    VALUES (?, ?, ?, ?, ?, ?, ?)
  `;

  db.execute(insertQuery, [user_id, study_date, start_time, end_time, duration_minutes, word_count, session_type], (err, result) => {
    if (err) {
      console.error('添加学习日志失败:', err);
      return res.status(500).json({ success: false, message: '服务器错误' });
    }

    // 返回成功信息
    res.json({ success: true, message: '学习日志记录成功', log_id: result.insertId });
  });
});



// 查询用户每月学习单词总数的接口
app.get('/api/stats/:userId/monthly-word-count', (req, res) => {
  const userId = req.params.userId;

  const query = `
    SELECT 
      DATE_FORMAT(study_date, '%Y-%m') AS month,
      SUM(word_count) AS total_word_count
    FROM 
      user_study_log
    WHERE 
      user_id = ?
    GROUP BY 
      DATE_FORMAT(study_date, '%Y-%m')
    ORDER BY 
      month;
  `;

  db.query(query, [userId], (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).json({ error: '数据库查询失败' });
    }

    res.json({ userId, stats: results });
  });
});

// 获取用户的熟练度分布（0~6等级）
app.get('/api/stats/:userId/proficiency-distribution', (req, res) => {
  const userId = req.params.userId;

  const sql = `
    SELECT 
      l.level AS proficiency,
      COUNT(uw.id) AS count
    FROM (
      SELECT 0 AS level UNION ALL
      SELECT 1 UNION ALL
      SELECT 2 UNION ALL
      SELECT 3 UNION ALL
      SELECT 4 UNION ALL
      SELECT 5 UNION ALL
      SELECT 6
    ) l
    LEFT JOIN user_words uw 
      ON uw.proficiency = l.level AND uw.user_id = ?
    GROUP BY l.level
    ORDER BY l.level
  `;

  db.query(sql, [userId], (err, results) => {
    if (err) {
      console.error('查询熟练度分布失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    res.json({
      userId,
      proficiencyStats: results
    });
  });
});

// 获取某个单词的例句列表
app.get('/api/words/:wordId/sentences', (req, res) => {
  const wordId = req.params.wordId;

  const query = 'SELECT id, sentence, translation FROM word_sentences WHERE word_id = ?';

  db.query(query, [wordId], (err, results) => {
    if (err) {
      console.error('获取例句失败:', err);
      return res.status(500).json({ error: '获取例句失败' });
    }

    res.json({ sentences: results });
  });
});

// 查询某个单词的同义词和反义词
app.get('/api/words/:wordId/synonyms-antonyms', (req, res) => {
  const wordId = req.params.wordId;
  // console.log('请求的 wordId:', wordId);

  const synonymQuery = 'SELECT synonym FROM word_synonyms WHERE word_id = ?';
  const antonymQuery = 'SELECT antonym FROM word_antonyms WHERE word_id = ?';

  // 并行查询同义词和反义词
  Promise.all([
    new Promise((resolve, reject) => {
      db.query(synonymQuery, [wordId], (err, results) => {
        if (err) {
          console.error('查询同义词失败:', err);
          reject(err);
        } else {
          // 直接返回 JSON 字符串，无需解析
          const synonymData = results[0]?.synonym || '[]';  // 如果没有同义词，返回一个空数组的 JSON 字符串
          resolve(synonymData);
        }
      });
    }),
    new Promise((resolve, reject) => {
      db.query(antonymQuery, [wordId], (err, results) => {
        if (err) {
          console.error('查询反义词失败:', err);
          reject(err);
        } else {
          // 直接返回 JSON 字符串，无需解析
          const antonymData = results[0]?.antonym || '[]';  // 如果没有反义词，返回一个空数组的 JSON 字符串
          resolve(antonymData);
        }
      });
    })
  ])
    .then(([synonyms, antonyms]) => {
      // 返回同义词和反义词的 JSON 字符串
      res.json({ synonyms, antonyms });
    })
    .catch((err) => {
      console.error('❌ 获取同义词/反义词失败:', err);
      res.status(500).json({ error: '查询失败' });
    });
});

// 获取单词的所有同义词和反义词
app.get('/api/words/all-synonyms-antonyms', (req, res) => {
  const synonymQuery = 'SELECT word_id, synonym FROM word_synonyms';
  const antonymQuery = 'SELECT word_id, antonym FROM word_antonyms';

  Promise.all([
    new Promise((resolve, reject) => {
      db.query(synonymQuery, (err, results) => {
        if (err) {
          console.error('查询同义词失败:', err);
          reject(err);
        } else {
          const synonymMap = {};
          results.forEach(row => {
            synonymMap[row.word_id] = row.synonym || '[]'; // 返回 JSON 字符串
          });
          resolve(synonymMap);
        }
      });
    }),
    new Promise((resolve, reject) => {
      db.query(antonymQuery, (err, results) => {
        if (err) {
          console.error('查询反义词失败:', err);
          reject(err);
        } else {
          const antonymMap = {};
          results.forEach(row => {
            antonymMap[row.word_id] = row.antonym || '[]'; // 返回 JSON 字符串
          });
          resolve(antonymMap);
        }
      });
    })
  ])
    .then(([synonyms, antonyms]) => {
      res.json({ synonyms, antonyms });
    })
    .catch(err => {
      console.error('❌ 获取全部同义词/反义词失败:', err);
      res.status(500).json({ error: '查询失败' });
    });
});


// 获取用户详细数据的 API
app.get('/api/users/details/:id', (req, res) => {
  const userId = req.params.id;  // 从路由参数中获取 userId
  // console.log(`获取用户详细数据: ${userId}`);
  const query = `
    SELECT 
        users.id AS user_id,
        users.username,
        users.email,
        users.phone,
        users.avatar,
        users.created_at AS user_created_at,
        users.status,
        user_profiles.id AS profile_id,
        user_profiles.nickname,
        user_profiles.bio,
        user_profiles.gender,
        user_profiles.birthday,
        user_profiles.location,
        user_profiles.profession,
        user_profiles.created_at AS profile_created_at,
        user_profiles.updated_at AS profile_updated_at
    FROM users
    LEFT JOIN user_profiles ON users.id = user_profiles.user_id
    WHERE users.id = ?;
  `;

  db.query(query, [userId], (err, results) => {
    if (err) {
      console.error('查询失败:', err);
      return res.status(500).send('服务器错误');
    }

    if (results.length === 0) {
      return res.status(404).send('用户未找到');
    }

    res.json(results[0]);  // 返回用户详细数据
  });
});



app.put('/api/users/details/:id', (req, res) => {
  const userId = req.params.id;
  const {
    email,
    phone,
    avatar,
    nickname,
    bio,
    gender,
    birthday,
    location,
    profession
  } = req.body;
  // console.log(req.body);
  // 更新 users 表
  const updateUserQuery = `
    UPDATE users 
    SET email = ?, phone = ?, avatar = ? 
    WHERE id = ?;
  `;

  // 尝试更新 user_profiles，如果不存在则插入
  const updateProfileQuery = `
    INSERT INTO user_profiles 
      (user_id, nickname, bio, gender, birthday, location, profession) 
    VALUES (?, ?, ?, ?, ?, ?, ?)
    ON DUPLICATE KEY UPDATE 
      nickname = VALUES(nickname),
      bio = VALUES(bio),
      gender = VALUES(gender),
      birthday = VALUES(birthday),
      location = VALUES(location),
      profession = VALUES(profession),
      updated_at = CURRENT_TIMESTAMP;
  `;

  db.beginTransaction((err) => {
    if (err) {
      console.error('开启事务失败:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    db.query(updateUserQuery, [email, phone, avatar, userId], (err) => {
      if (err) {
        return db.rollback(() => {
          console.error('更新用户表失败:', err);
          res.status(500).json({ message: '更新用户信息失败' });
        });
      }

      db.query(
        updateProfileQuery,
        [userId, nickname, bio, gender, birthday, location, profession],
        (err) => {
          if (err) {
            return db.rollback(() => {
              console.error('更新用户资料失败:', err);
              res.status(500).json({ message: '更新用户资料失败' });
            });
          }

          db.commit((err) => {
            if (err) {
              return db.rollback(() => {
                console.error('提交事务失败:', err);
                res.status(500).json({ message: '提交失败' });
              });
            }

            res.json({ success: true, message: '用户信息更新成功' });
          });
        }
      );
    });
  });
});

app.post('/api/error-word', (req, res) => {
  const { userId, wordId } = req.body;
  const errorDate = new Date().toLocaleDateString('zh-CN'); // 获取今天的日期

  if (!userId || !wordId) {
    return res.status(400).json({ message: '用户ID和单词ID为必填项' });
  }

  const query = `
    SELECT * FROM error_words_statistics 
    WHERE user_id = ? AND word_id = ? AND error_date = ?
  `;

  // 检查是否已记录该用户的错误
  db.query(query, [userId, wordId, errorDate], (err, results) => {
    if (err) {
      console.error('查询错误:', err);
      return res.status(500).json({ message: '服务器错误' });
    }

    if (results.length > 0) {
      // 如果已有记录，更新错误次数
      const updateQuery = `
        UPDATE error_words_statistics 
        SET error_count = error_count + 1 
        WHERE user_id = ? AND word_id = ? AND error_date = ?
      `;
      db.query(updateQuery, [userId, wordId, errorDate], (err, result) => {
        if (err) {
          console.error('更新错误记录失败:', err);
          return res.status(500).json({ message: '服务器错误' });
        }
        return res.json({ message: '错误记录更新成功' });
      });
    } else {
      // 如果没有记录，插入新记录
      const insertQuery = `
        INSERT INTO error_words_statistics (user_id, word_id, error_count, error_date)
        VALUES (?, ?, 1, ?)
      `;
      db.query(insertQuery, [userId, wordId, errorDate], (err, result) => {
        if (err) {
          console.error('插入错误记录失败:', err);
          return res.status(500).json({ message: '服务器错误' });
        }
        return res.json({ message: '错误记录插入成功' });
      });
    }
  });
});


// 获取最近一天错误最多的三个单词及其详细数据
app.get('/api/most-error-words', (req, res) => {
  const collectedWords = new Map(); // 用于去重和收集
  let offsetDays = 0;

  const fetchWordsByDate = (date) => {
    return new Promise((resolve, reject) => {
      const query = `
        SELECT e.word_id, SUM(e.error_count) AS total_errors
        FROM error_words_statistics e
        JOIN (
            SELECT DISTINCT user_id, word_id, error_date
            FROM error_words_statistics
            WHERE error_date = ?
        ) AS unique_errors
        ON e.user_id = unique_errors.user_id
        AND e.word_id = unique_errors.word_id
        AND e.error_date = unique_errors.error_date
        GROUP BY e.word_id
        ORDER BY total_errors DESC;
      `;
      db.query(query, [date], (err, results) => {
        if (err) return reject(err);
        resolve(results.map(r => ({ ...r, date })));
      });
    });
  };

  const enrichWord = (wordObj) => {
    const wordId = wordObj.word_id;

    const wordQuery = `SELECT id, word, definition FROM words WHERE id = ?`;
    const sentenceQuery = `SELECT sentence, translation FROM word_sentences WHERE word_id = ?`;

    return new Promise((resolve, reject) => {
      db.query(wordQuery, [wordId], (err, wordDetails) => {
        if (err) return reject(err);
        db.query(sentenceQuery, [wordId], (err, sentences) => {
          if (err) return reject(err);
          resolve({
            ...wordObj,
            wordDetails: wordDetails[0],
            sentences: sentences
          });
        });
      });
    });
  };

  const tryFetch = async () => {
    while (collectedWords.size < 3 && offsetDays < 30) {
      const date = new Date();
      date.setDate(date.getDate() - offsetDays);
      const formattedDate = date.toISOString().split('T')[0];

      try {
        const results = await fetchWordsByDate(formattedDate);
        for (const result of results) {
          if (!collectedWords.has(result.word_id)) {
            collectedWords.set(result.word_id, result);
          }
          if (collectedWords.size === 3) break;
        }
      } catch (err) {
        console.error('查询错误:', err);
        return res.status(500).json({ message: '服务器错误' });
      }

      offsetDays++;
    }

    if (collectedWords.size === 0) {
      return res.status(404).json({ message: '近30天没有错误单词记录' });
    }

    try {
      const wordArray = Array.from(collectedWords.values());
      const enriched = await Promise.all(wordArray.map(enrichWord));
      res.json(enriched);
    } catch (err) {
      console.error('详细信息查询失败:', err);
      res.status(500).json({ message: '服务器错误' });
    }
  };

  tryFetch();
});






// 读取 Excel 文件并返回加密数据
app.get('/data', (req, res) => {
  try {
    const filePath = path.join(__dirname, './Data/Data.xls');
    const workbook = xlsx.readFile(filePath);
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    const jsonData = xlsx.utils.sheet_to_json(worksheet, { header: 1 });
    const encryptedData = CryptoJS.AES.encrypt(JSON.stringify(jsonData), SECRET_KEY).toString();
    res.json({ success: true, data: encryptedData });
  } catch (error) {
    console.error('读取 Excel 失败:', error);
    res.status(500).json({ success: false, message: '服务器错误' });
  }
});

// AI 聊天接口，调用外部 API
app.post('/ai-chat', async (req, res) => {
  try {
    // console.log(req.body.messages);
    const data = req.body;
    // console.log('Request Body:', data);
    // 组装请求体
    const requestBody = {
      model: "4.0Ultra",
      messages: data.messages,
      stream: false,
      max_tokens: 1024
    };
    // 发送请求到 AI API
    const response = await axios.post(
      'https://spark-api-open.xf-yun.com/v1/chat/completions',
      requestBody,
      {
        headers: {
          'Authorization': 'Bearer joRSxASbPrOtUadecqgA:OVtblvXIkRXQZGDhUEyw',
          'Content-Type': 'application/json',
        }
      }
    );
    res.json(response.data);
  } catch (error) {
    console.error('Error:', error.response?.data || error);
    res.status(500).json({ error: 'Internal Server Error' });
  }
});

// 翻译 API 封装接口
app.post('/api/translate', async (req, res) => {
  const { text, from, to } = req.body;

  // 参数校验
  if (!text || !from || !to) {
    return res.status(400).json({
      code: 400,
      data: null,
      msg: '参数缺失: text, from, to 必填',
    });
  }

  try {
    // 调用外部翻译接口
    const response = await axios.get('https://translate.appworlds.cn', {
      params: { text, from, to },
    });

    res.json(response.data); // 返回翻译结果
  } catch (error) {
    res.status(500).json({
      code: 500,
      data: null,
      msg: '翻译请求失败，请稍后重试',
    });
  }
});

// 查询单词定义 API 封装接口
app.get('/api/define/:word', async (req, res) => {
  const { word } = req.params;
  // console.log(`查询单词定义: ${word}`);
  try {

    const response = await axios.get(`https://api.dictionaryapi.dev/api/v2/entries/en/${word}`);
    // console.log(response.data[0]);
    const data = response.data[0];

    res.json({
      success: true,
      data: data
    });
  } catch (error) {
    console.error('词典查询失败:', error.message);
    res.status(404).json({
      success: false,
      message: `❌ 未找到 ${word}，请检查拼写！`
    });
  }
});





// 启动服务器
app.listen(PORT, () => {
  console.log(`🚀 服务器运行在 http://localhost:${PORT}`);
});